--Function of adding new index
create or replace procedure meta_add_index(
    --Index id
    a_index_id raw,
    --Table id
    a_table_id raw,
    --Is unique
    a_is_unique number,
    --Column id list
    a_column_id_list raw)
as
    --Table name
    a_table_name varchar2(26);
    --Column list
    a_column_list varchar2(32767) := '';
    --Column id
    a_column_id raw(16);
    --Column name
    a_column_name varchar2(26);
    --Current index
    a_curr_index number := 1;
    --Current position
    a_curr_position number := 1;
    --Item length
    a_item_length number := 16;
    --Row count
    a_row_count number;
begin

    --If there is no column specified
    if a_column_id_list is null then
        return;
    end if;

    --Searching for the index with the same id
    select count(*) into a_row_count
    from meta_indexes
    where index_id = a_index_id;
    
    if a_row_count > 0 then
        raise_application_error(-20028,
                'A index with the specified id was already added (index_id = '
                || a_index_id
                || ') (#error_code = 20028)');
    end if;
    
    --Searching for the index with the same column set
    select count(*) into a_row_count
    from meta_indexes
    where column_id_list = a_column_id_list;
    
     if a_row_count > 0 then
        raise_application_error(-20029,
                'A index with the specified column set was already added (#error_code = 20029)');
    end if;
    
    --Reading table name
    a_table_name := p_get_table_name(a_table_id);
    
    --Reading user columns
    loop
        exit when dbms_lob.getlength(a_column_id_list) - a_curr_position + 1 < 16;
        
        dbms_lob.read(a_column_id_list, a_item_length, a_curr_position, a_column_id);
        
        a_curr_index := a_curr_index + 1;
        a_curr_position := a_curr_position + 16;
            
        --Reading column name
        select column_name into a_column_name
        from meta_table_columns
        where table_id = a_table_id
        and column_id = a_column_id;
            
        if length(a_column_list) > 0 then
            a_column_list := a_column_list || ',';
        end if;
            
        a_column_list := a_column_list || a_column_name;
    end loop;
    
    --Creating the index
    execute immediate 'create index i'
    || substr(a_index_id, 1, 29)
    || ' on tbl_'  || a_table_name
    || ' (' || a_column_list || ')';
    
    --Updating the metadata
    insert into meta_indexes(index_id, table_id, is_unique, column_id_list)
    values (a_index_id, a_table_id, a_is_unique, a_column_id_list);
    
    --If the index is unique
    if a_is_unique = 1 then
        p_recreate_index_triggers(a_index_id);
    end if;
end;

--go

--Function that recreates the index triggers
create or replace procedure p_recreate_index_triggers(
    --Index id
    a_index_id raw)
as
    --Table id
    a_table_id raw(16);
    --Is unique
    a_is_unique number;
    --Trigger column condition
    a_column_condition varchar2(32767) := '';
    --Column list
    a_column_list varchar2(32767) := 'is_active';
    --Table name
    a_table_name varchar2(26);
    --Column id list
    a_column_id_list raw(8000);
    --Column id
    a_column_id raw(16);
    --Column name
    a_column_name varchar2(26);
    --Current index
    a_curr_index number := 1;
    --Current position
    a_curr_position number := 1;
    --Item length
    a_item_length number := 16;
begin
    --Reading index properties
    select table_id, is_unique, column_id_list into a_table_id, a_is_unique, a_column_id_list
    from meta_indexes
    where index_id = a_index_id;
    
    --Reading table name
    a_table_name := p_get_table_name(a_table_id);
    
    --If the index is unique
    if a_is_unique = 1 then
        --Reading user columns
        loop
            exit when dbms_lob.getlength(a_column_id_list) - a_curr_position + 1 < 16;
            
            dbms_lob.read(a_column_id_list, a_item_length, a_curr_position, a_column_id);
            
            a_curr_index := a_curr_index + 1;
            a_curr_position := a_curr_position + 16;
                
            --Reading column name
            select column_name into a_column_name
            from meta_table_columns
            where table_id = a_table_id
            and column_id = a_column_id;
            
            --If it is not the is_active column which is handled automatically
            if a_column_name <> 'is_active' then               
                a_column_list := a_column_list || ','  || a_column_name;
            end if;
            
            if length(a_column_condition) > 0 then  
                a_column_condition := a_column_condition || ' and ';
            end if;

            a_column_condition := a_column_condition || 's.' 
            || a_column_name || '=t.' || a_column_name;
        end loop;
    
        --Creating the trigger that registers modified rows
        execute immediate 'create or replace trigger z' || substr(a_index_id, 1, 29)
        || ' after insert or update of ' || a_column_list
        || ' on tbl_' || a_table_name || ' for each row'
        || ' declare'
        || ' begin'
        || '   insert into meta_modified_rows(table_id, row_id) values(''' || a_table_id || ''', :new.id);'
        || ' end;';
        
        --Creating the trigger that enforces uniqueness
        execute immediate 'create or replace trigger q' || substr(a_index_id, 1, 29)
        || ' after insert or update of ' || a_column_list
        || ' on tbl_' || a_table_name
        || ' declare'
        || ' a_recycle_bin number;'
        || ' a_row_count number;'
        || ' begin'
            --Checking the recycle bin availability
        || '   select int_value into a_recycle_bin from meta_options where option_name = ''RECYCLE_BIN'';'
        || '   for item in (select row_id from meta_modified_rows where table_id = hextoraw(''' || a_table_id || ''')) loop'
                  --If the recycle bin is enabled
        || '     if a_recycle_bin = 1 then'
                    --Searching for another row with the same column values in active rows
        || '        select count(*) into a_row_count from tbl_' || a_table_name || ' s'
        || '        inner join tbl_' || a_table_name || ' t on t.id=item.row_id'
        || '        where s.is_active = 1 and ' || a_column_condition || ';'
            --If the recycle bin is not enabled
        || '     else'
                  --Searching for another row with the same column values in all rows
        || '        select count(*) into a_row_count from tbl_' || a_table_name || ' s'
        || '        inner join tbl_' || a_table_name || ' t on t.id=item.row_id'
        || '        where ' || a_column_condition || ';'
        || '     end if;'
        || '     if a_row_count > 1 then'
        || '        raise_application_error(-20055,'
        || '          ''The operation results in breaking the column values uniqueness (id = '' || item.row_id || '') (#error_code = 20055)'');'
        || '     end if;'
        || '   end loop;'
        || ' end;';
    end if;
end;

--go

--Function that recreates the column index triggers
create or replace procedure p_recreate_col_index_triggers(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
begin
    --Reading column properties
    select table_id
    into a_table_id
    from meta_table_columns
    where column_id = a_column_id;
    
    for i in (select index_id, column_id_list from meta_indexes
        where table_id = a_table_id and is_unique = 1) loop
        
        if p_column_is_indexed(i.index_id, a_column_id) = 1 then
           p_recreate_index_triggers(i.index_id);
        end if;
        
    end loop;
end;

--go

--Function of checking if column is indexed
create or replace function p_column_is_indexed(
    --Index id
    a_index_id raw,
    --Column id
    a_column_id raw)
return number
as
    --Index column id list
    a_column_id_list blob;
    --Position
    a_position number;
begin
    --Reading column id list
    select column_id_list
    into a_column_id_list
    from meta_indexes
    where index_id = a_index_id;
    
    a_position := dbms_lob.instr(a_column_id_list, a_column_id);
    
    if a_position = 0 then
        return 0;
    elsif (a_position - 1) mod 16 = 0 then
        return 1;
    else
        return 0;
    end if;
end;

--go

--Function of deleting all indexes that include the column
create or replace procedure p_delete_column_indexes(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
begin
    --Reading column properties
    select table_id
    into a_table_id
    from meta_table_columns
    where column_id = a_column_id;
    
    for i in (select index_id, column_id_list from meta_indexes
        where table_id = a_table_id) loop
        
        if p_column_is_indexed(i.index_id, a_column_id) = 1 then
           meta_delete_index(i.index_id);
        end if;
        
    end loop;
end;

--go

--Function of dropping table index
create or replace procedure meta_delete_index(
    --Index id
    a_index_id raw)
as
    --Row count
    a_row_count number;
    --Is unique
    a_is_unique number;
begin
    --Searching for the index with the same id
    select count(*) into a_row_count
    from meta_indexes
    where index_id = a_index_id;
    
    if a_row_count = 0 then
        raise_application_error(-20030,
                'A index with the specified id was not found (index_id = '
                || a_index_id
                || ') (#error_code = 20030)');
    end if;
    
    --Checking if the index is unique
    select is_unique into a_is_unique
    from meta_indexes
    where index_id = a_index_id;
    
    --Dropping the index
    execute immediate 'drop index i'
    || substr(a_index_id, 1, 29);
    
    --If the index is unique
    if a_is_unique = 1 then
        --Dropping the triggers
        execute immediate 'drop trigger q' || substr(a_index_id, 1, 29);
        execute immediate 'drop trigger z' || substr(a_index_id, 1, 29);
    end if;
    
    --Updating the metadata
    delete from meta_indexes
    where index_id = a_index_id;
end;

--go